package com.c2soft.nepalcmx.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import com.c2soft.nepalcmx.command.AccountUserCommand;
import com.c2soft.nepalcmx.command.BrokerFormCommand;



public class UserFormDAO {
	
	@Autowired
	private DataSource dataSource;
	
	private JdbcTemplate jdbcTemplate;
	
	
	

	public List<BrokerFormCommand> findAllUsers(String userId) {
		
		String sql = "select * from brokers where USER_ID=?";
		jdbcTemplate = new JdbcTemplate(dataSource);
		List<BrokerFormCommand> userdetail=jdbcTemplate.query(sql,new Object[]{userId},new BeanPropertyRowMapper(BrokerFormCommand.class));
		
		return userdetail;

	}
	
public List<BrokerFormCommand> findExchangeDetail() {
		
		String sql = "select * from users where USER_ID='exchange'";
		jdbcTemplate = new JdbcTemplate(dataSource);
		List<BrokerFormCommand> userdetail=jdbcTemplate.query(sql,new BeanPropertyRowMapper(BrokerFormCommand.class));
		return userdetail;

	}

	
	public List<BrokerFormCommand> findBrokersList() {
		String sql = "select USER_ID from user_roles where USER_ROLE_ID ='BROKER'";
		jdbcTemplate = new JdbcTemplate(dataSource);
		List<BrokerFormCommand> userList=jdbcTemplate.query(sql,new BorkerIDMapper());
		return userList;

	}
	
		public List<BrokerFormCommand> findAllUserList() {
	String sql = "select * from users ";
	jdbcTemplate = new JdbcTemplate(dataSource);
	List<BrokerFormCommand> userList=jdbcTemplate.query(sql,new BeanPropertyRowMapper(BrokerFormCommand.class));
	return userList;

	}

public void savepwdUpdateService(BrokerFormCommand update) {
	String sql = "update users set PASSWORD='"+update.getPassword()+"' where USER_ID='"+ update.getUserId()+"' ";
	String sql1 = "update brokers set PASSWORD='"+update.getPassword()+"' where USER_ID='"+ update.getUserId()+"' ";
	jdbcTemplate = new JdbcTemplate(dataSource);
	jdbcTemplate.update(sql);
	jdbcTemplate.update(sql1);
	
}

public void saveBrokerpwdUpdateService(BrokerFormCommand update) {
	String sql = "update users set PASSWORD='"+update.getPassword()+"' where USER_ID='"+ update.getUserId()+"' ";
	String sql1 = "update brokers set PASSWORD='"+update.getPassword()+"' where BROKER_ID='"+ update.getbrokerId()+"' and USER_ID ='' ";
	jdbcTemplate = new JdbcTemplate(dataSource);
	jdbcTemplate.update(sql);
	jdbcTemplate.update(sql1);

}

public void saveExchangepwdUpdateService(BrokerFormCommand update) {
	

	String sql = "update users set PASSWORD='"+update.getPassword()+"' where USER_ID='exchange' ";
	String sql1 = "update brokers set PASSWORD='"+update.getPassword()+"' where USER_ID='' and broker_id='' ";
	jdbcTemplate = new JdbcTemplate(dataSource);
	jdbcTemplate.update(sql);
	jdbcTemplate.update(sql1);
	
}

public void saveTranspwdUpdateService(BrokerFormCommand update) {
	String sql = "update users set TRANSPWD='"+update.getTranspwd()+"' where USER_ID='"+ update.getUserId()+"' ";
	String sql1 = "update brokers set TRANSPWD='"+update.getTranspwd()+"' where USER_ID='"+ update.getUserId()+"' ";
	
	jdbcTemplate = new JdbcTemplate(dataSource);
	jdbcTemplate.update(sql	);
	jdbcTemplate.update(sql1);
	
}

public void saveBrokerTranspwdUpdateService(BrokerFormCommand update) {
	String sql = "update users set TRANSPWD='"+update.getTranspwd()+"' where USER_ID='"+ update.getUserId()+"' ";
	String sql1 = "update brokers set TRANSPWD='"+update.getTranspwd()+"' where BROKER_ID='"+ update.getbrokerId()+"' and USER_ID='' ";
	
	jdbcTemplate = new JdbcTemplate(dataSource);
	jdbcTemplate.update(sql	);
	jdbcTemplate.update(sql1);
	
}

public void saveExchangeTranspwdUpdateService(BrokerFormCommand update) {
	String sql = "update users set TRANSPWD='"+update.getTranspwd()+"' where USER_ID='exchange' ";
	String sql1 = "update brokers set TRANSPWD='"+update.getTranspwd()+"' where USER_ID='' and broker_id='' ";
	
	jdbcTemplate = new JdbcTemplate(dataSource);
	jdbcTemplate.update(sql	);
	jdbcTemplate.update(sql1);
	
}

public void updateUserDetailService(BrokerFormCommand user) {
			String sql = "UPDATE brokers SET PASSWORD='"+user.getPassword()+"',TRANSPWD='"+user.getTranspwd()+"'"+
									" WHERE USER_ID='"+ user.getUserId()+"'";
			String sql1 = "UPDATE users SET PASSWORD='"+user.getPassword()+"',TRANSPWD='"+user.getTranspwd()+"'"+
					" WHERE USER_ID='"+ user.getUserId()+"'";
			jdbcTemplate = new JdbcTemplate(dataSource);
			jdbcTemplate.update(sql);
			jdbcTemplate.update(sql1);
			
			
		}

		private final class BorkerIDMapper implements RowMapper<BrokerFormCommand> {

		    public BrokerFormCommand mapRow(ResultSet rs, int rowNum) throws SQLException {
		    	BrokerFormCommand user = new BrokerFormCommand();
		    	user.setUserId(rs.getString(1));
		        return user;
		    } 
		}
		
		public void updateUserAccountService(BrokerFormCommand update) {
			String sql = "update users set fname=? , lname=? , emailID=? , phone=? where user_ID=? ";
			String sql1 = "update brokers set fname=? , lname=? , emailID=? , phone=? where user_ID=?  ";
			
			jdbcTemplate = new JdbcTemplate(dataSource);
			jdbcTemplate.update(sql,new Object[]{update.getFname(),update.getLname(),update.getEmailID(),update.getPhone(),update.getUserId()}	);
			jdbcTemplate.update(sql1,new Object[]{update.getFname(),update.getLname(),update.getEmailID(),update.getPhone(),update.getUserId()}	);
			
		}
		
		public void updateBrokerAccountService(BrokerFormCommand update) {
			
			String sql = "update users set fname=? , lname=? , emailID=? , phone=? where user_ID=? ";
			String sql1 = "update brokers set fname=? , lname=? , emailID=? , phone=? where broker_ID=? and user_ID='' ";
			
			jdbcTemplate = new JdbcTemplate(dataSource);
			
			jdbcTemplate.update(sql,new Object[]{update.getFname(),update.getLname(),update.getEmailID(),update.getPhone(),update.getbrokerId()}	);
			
			jdbcTemplate.update(sql1,new Object[]{update.getFname(),update.getLname(),update.getEmailID(),update.getPhone(),update.getbrokerId()}	);
			
		}
		
		public List<AccountUserCommand> findUserDetail(String userId) {
			
			String sql = "select * from brokers where USER_ID=?";
			jdbcTemplate = new JdbcTemplate(dataSource);
			List<AccountUserCommand> userdetail=jdbcTemplate.query(sql,new Object[]{userId},new BeanPropertyRowMapper(AccountUserCommand.class));
			
			return userdetail;

		}
		
		public List<AccountUserCommand> findBrokerDetail(String userId) {
			
			String sql = "select * from brokers where user_id='' and BROKER_ID=(Select broker_id from brokers where user_id=?)";
			jdbcTemplate = new JdbcTemplate(dataSource);
			List<AccountUserCommand> userdetail=jdbcTemplate.query(sql,new Object[]{userId},new BeanPropertyRowMapper(AccountUserCommand.class));
			
			return userdetail;

		}

		public void postRMSNotification(BrokerFormCommand user) {
				String sql="insert into notification(USER_ID,BROKER_ID,MSG) values(?,?,?)";
				jdbcTemplate = new JdbcTemplate(dataSource);
				jdbcTemplate.update(sql,
						new Object[] { user.getUserId(),user.getbrokerId(),user.getMsg()});
		}
		
		public void postBrokerRMSNotification(BrokerFormCommand user) {
			String sql="insert into notification(BROKER_ID,MSG) values(?,?)";
			jdbcTemplate = new JdbcTemplate(dataSource);
			jdbcTemplate.update(sql,
					new Object[] { user.getbrokerId(),user.getMsg()});
			

	}
		
		public List<BrokerFormCommand> findRMSReportForUser(String userId) {
			
			String sql = "select msg,isview,id,user_id from notification where user_id=? and DATE(ts)=CURDATE() and isview='0'";
			jdbcTemplate = new JdbcTemplate(dataSource);
			List<BrokerFormCommand> userdetail=jdbcTemplate.query(sql,new Object[]{userId},new RMSMapperForUser());
			
			return userdetail;

		}

		private final class RMSMapperForUser implements RowMapper<BrokerFormCommand> {	

			public BrokerFormCommand mapRow(ResultSet rs, int rowNum) throws SQLException {
				BrokerFormCommand rms = new BrokerFormCommand();
				rms.setMsg(rs.getString(1));
				rms.setStatus(rs.getString(2));
				rms.setIdStatus(String.valueOf(rs.getString(3)));
				rms.setUserId(rs.getString(4));
				int rowCount = jdbcTemplate.queryForInt("select count(*) from notification where user_Id='"+rms.getUserId()+"' and DATE(ts)=CURDATE() and isview=0");
				rms.setCountmsg(String.valueOf(rowCount));
				
				return rms;
			} 
			}
		
		public void updateStatusUser(String  update) {
			
			String sql = "update notification set isview='1' where id='"+update+"'";
			jdbcTemplate = new JdbcTemplate(dataSource);
			
			jdbcTemplate.update(sql	);
			
		}
		
		public List<BrokerFormCommand> updateStatusUser1(String  userId) {
			
			String sql = "update notification set isview='1' where user_id='"+userId+"'";
			jdbcTemplate = new JdbcTemplate(dataSource);
			jdbcTemplate.update(sql	);
			String sql1="select msg from notification where user_id='"+userId+"' and DATE(ts)=CURDATE() ORDER BY ts DESC";
			List<BrokerFormCommand> rmsMsg=jdbcTemplate.query(sql1,new RMSMapperForUser1());
			
			return rmsMsg;
		}
		
		private final class RMSMapperForUser1 implements RowMapper<BrokerFormCommand> {	

			public BrokerFormCommand mapRow(ResultSet rs, int rowNum) throws SQLException {
				BrokerFormCommand rms = new BrokerFormCommand();
				rms.setMsg(rs.getString(1));
				return rms;
			} 
			}
		
		public List<BrokerFormCommand> updateStatusForBroker1(String  brokerId) {
			
			String sql = "update notification set isview='1' where broker_id='"+brokerId+"' and user_id=''";
			jdbcTemplate = new JdbcTemplate(dataSource);
			jdbcTemplate.update(sql	);
			String sql1="select msg from notification where broker_id='"+brokerId+"' and user_id='' and DATE(ts)=CURDATE() ORDER BY ts DESC";
			List<BrokerFormCommand> rmsMsg=jdbcTemplate.query(sql1,new RMSMapperForUser1());
			
			return rmsMsg;
		}
		
		public List<BrokerFormCommand> findRMSReportForBroker(String brokerId) {
			
			String sql = "select msg,isview,id,broker_Id from notification where broker_Id=? and user_id='' and DATE(ts)=CURDATE() and isview='0'";
			jdbcTemplate = new JdbcTemplate(dataSource);
			List<BrokerFormCommand> userdetail=jdbcTemplate.query(sql,new Object[]{brokerId},new RMSMapperForBroker());
			return userdetail;

		}

		private final class RMSMapperForBroker implements RowMapper<BrokerFormCommand> {	

			public BrokerFormCommand mapRow(ResultSet rs, int rowNum) throws SQLException {
				BrokerFormCommand rms = new BrokerFormCommand();
				rms.setMsg(rs.getString(1));
				rms.setStatus(rs.getString(2));
				rms.setIdStatus(String.valueOf(rs.getString(3)));
				rms.setbrokerId(rs.getString(4));
				int rowCount = jdbcTemplate.queryForInt("select count(*) from notification where broker_Id='"+rms.getbrokerId()+"' and user_id='' and DATE(ts)=CURDATE() and isview=0");
				rms.setCountmsg(String.valueOf(rowCount));
				return rms;
			} 
			}
		
		public void forgetPassword(BrokerFormCommand forgetPwd) {
			
			String sql_users = "update users set password='"+forgetPwd.getPassword()+"' where user_id='"+forgetPwd.getUserId()+"'";
			jdbcTemplate = new JdbcTemplate(dataSource);
			jdbcTemplate.update(sql_users);
			
			String userRole = this.jdbcTemplate.queryForObject(
			        "select authority from user_roles where user_id = ?", 
			        new Object[]{forgetPwd.getUserId()}, String.class);
			
			
			String sql="";
			
			if(userRole.equals("ROLE_BROKER")){
				sql="update brokers set password='"+forgetPwd.getPassword()+"' where broker_id='"+forgetPwd.getUserId()+"' and user_id=''";
			}
			else if(userRole.equals("ROLE_EXCHANGE")){
				sql="update brokers set password='"+forgetPwd.getPassword()+"' where user_id='' and broker_id=''";
			}
			else if(userRole.equals("ROLE_USER")){
				sql="update brokers set password='"+forgetPwd.getPassword()+"' where user_id='"+forgetPwd.getUserId()+"'";
			}
			jdbcTemplate.update(sql);		
			
			
		}
		

}
